package zy.dao.sys.kpi.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sys.kpi.KpiDAO;
import zy.entity.sys.kpi.T_Sys_Kpi;
import zy.entity.sys.kpi.T_Sys_KpiScore;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class KpiDAOImpl extends BaseDaoImpl implements KpiDAO{
	@Override
	public List<T_Sys_Kpi> list(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT t.ki_id,t.ki_code,t.ki_shop_code,t.companyid,k.ki_name,k.ki_identity,k.ki_remark ");
		sql.append(" FROM t_sys_kpi t");
		sql.append(" JOIN common_kpi k ON k.ki_code = t.ki_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND ki_shop_code = :shop_code");
		if (StringUtil.isNotEmpty(params.get("searchContent"))) {
			sql.append(" AND (INSTR(k.ki_code,:searchContent)>0 OR INSTR(k.ki_name,:searchContent)>0)");
		}
		sql.append(" AND t.companyid=:companyid");
		if (StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))) {
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY ki_code ASC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sys_Kpi.class));
	}
	
	@Override
	public List<T_Sys_KpiScore> listScores(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ks_id,ks_ki_code,ks_min,ks_max,ks_score,ks_shop_code,ks_rw_code,companyid,");
		sql.append(" (SELECT rw_name FROM t_sys_reward rw WHERE rw_code = ks_rw_code AND rw.companyid = t.companyid LIMIT 1) AS reward_name");
		sql.append(" FROM t_sys_kpiscore t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ks_ki_code = :ki_code");
		sql.append(" AND ks_shop_code = :shop_code");
		sql.append(" AND t.companyid=:companyid");
		if (StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))) {
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY ks_min ASC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sys_KpiScore.class));
	}
	
	@Override
	public T_Sys_Kpi check(String ki_code, String shop_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ki_id,ki_code,ki_shop_code,companyid ");
		sql.append(" FROM t_sys_kpi t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ki_shop_code = :shop_code");
		sql.append(" AND ki_code = :ki_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("shop_code", shop_code).addValue("ki_code", ki_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sys_Kpi.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void save(T_Sys_Kpi kpi, List<T_Sys_KpiScore> kpiScores) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_sys_kpi");
		sql.append(" (ki_code,ki_shop_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ki_code,:ki_shop_code,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(kpi),holder);
		kpi.setKi_id(holder.getKey().intValue());
		sql.setLength(0);
		sql.append(" INSERT INTO t_sys_kpiscore");
		sql.append(" (ks_ki_code,ks_min,ks_max,ks_score,ks_shop_code,ks_rw_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ks_ki_code,:ks_min,:ks_max,:ks_score,:ks_shop_code,:ks_rw_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(kpiScores.toArray()));
	}
	
	@Override
	public void delete(String ki_code, String shop_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sys_kpi");
		sql.append(" WHERE ki_code=:ki_code");
		sql.append(" AND ki_shop_code = :shop_code");
		sql.append(" AND companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), 
				new MapSqlParameterSource().addValue("shop_code", shop_code).addValue("ki_code", ki_code).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteScores(String ki_code, String shop_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sys_kpiscore");
		sql.append(" WHERE ks_ki_code=:ki_code");
		sql.append(" AND ks_shop_code = :shop_code");
		sql.append(" AND companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), 
				new MapSqlParameterSource().addValue("shop_code", shop_code).addValue("ki_code", ki_code).addValue("companyid", companyid));
	}
	
	@Override
	public void saveScores(List<T_Sys_KpiScore> kpiScores) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_sys_kpiscore");
		sql.append(" (ks_ki_code,ks_min,ks_max,ks_score,ks_shop_code,ks_rw_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ks_ki_code,:ks_min,:ks_max,:ks_score,:ks_shop_code,:ks_rw_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(kpiScores.toArray()));
	}
	
}
